﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;

namespace PM.Services
{
    public class OrderServices
    {
        private static Order GetOrderFromDataReader(IDataReader dataReader)
        {
            Order retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                int intTemp = 0;
                double dblTemp =0;
                retVal = new Order();

                intTemp = 0;
                if (int.TryParse(dataReader["ORDER_ID"].ToString(), out intTemp))
                    retVal.OrderId = intTemp;

                retVal.ClienId = dataReader["CLIENT_ID"].ToString();
                retVal.SecurityId = dataReader["SECURITY_ID"].ToString();
                
                intTemp = 0;
                if (int.TryParse(dataReader["ORIGINAL_QTY"].ToString(), out intTemp))
                    retVal.OriginalQty = intTemp;

                dblTemp = 0;
                if (double.TryParse(dataReader["PRICE"].ToString(), out dblTemp))
                    retVal.Price = dblTemp;

                retVal.OrderDateTime = dataReader["ORDER_DATE_TIME"].ToString();
                retVal.OrderType = dataReader["ORDER_TYPE"].ToString();
                retVal.BrokerRef = dataReader["BROKER_REF"].ToString();
                retVal.Tif = dataReader["TIF"].ToString();

                intTemp = 0;
                if (int.TryParse(dataReader["TIF_DAYS"].ToString(), out intTemp))
                    retVal.TifDays = intTemp;

                retVal.LotType = dataReader["LOT_TYPE"].ToString();
                retVal.OriginalOrderId = dataReader["ORIGINAL_ORDER_ID"].ToString();
                retVal.TranId = dataReader["TRAN_ID"].ToString();
                retVal.BuySell = dataReader["BUY_SELL"].ToString();
                
                intTemp = 0;
                if (int.TryParse(dataReader["MIN_FILL_QTY"].ToString(), out intTemp))
                    retVal.MinFillQty = intTemp;

                intTemp = 0;
                if (int.TryParse(dataReader["FOREIGN_BROKER"].ToString(), out intTemp))
                    retVal.ForeignBroker = intTemp;
                
                retVal.TradeId = dataReader["TRADE_ID"].ToString();
                retVal.BrokerOffice = dataReader["BROKER_OFFICE"].ToString();

            }
            return retVal;
        }

        public static Order GetOrderById(int orderId)
        {
            Order retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ORDER_TAB WHERE ORDER_ID = " + orderId;
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetOrderFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static OrderCollection GetOrderCollection()
        {
            OrderCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ORDER_TAB";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new OrderCollection();
                    while (dataReader.Read())
                    {
                        Order item = GetOrderFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetOrderCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM ORDER_TAB";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextOrderId()
        {
            int retVal = 1;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT MAX(ORDER_ID) FROM ORDER_TAB";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {                        
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(ORDER_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;

        }

        public static int GetNextOrderId(OracleConnection con, OracleTransaction tran)
        {
            int retVal = 1;
            //OracleConnection con = new OracleConnection();

            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT MAX(ORDER_ID) FROM ORDER_TAB";
                cmd.Connection = con;
                cmd.Transaction = tran;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null && !dataReader.IsClosed && dataReader.Read())
                    {
                        int intTemp = 0;
                        if (int.TryParse(dataReader["MAX(ORDER_ID)"].ToString(), out intTemp))
                            retVal = intTemp + 1;
                    }
                    else
                    {
                        retVal = 1;
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
            return retVal;

        }


        public static void Add(Order order)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO ORDER_TAB" +
                                    "(ORDER_ID, CLIENT_ID, SECURITY_ID, ORIGINAL_QTY, PRICE, ORDER_DATE_TIME, ORDER_TYPE, BROKER_REF, TIF, TIF_DAYS, LOT_TYPE, ORIGINAL_ORDER_ID, TRAN_ID, BUY_SELL, MIN_FILL_QTY, FOREIGN_BROKER, TRADE_ID, BROKER_OFFICE )" +
                                    "VALUES(:v1, :v2,       :v3,        :v4,            :v5,        :v6,        :v7,        :v8,      :v9,  :v10,       :v11,   :v12,               :v13,   : v14,    :v15,           :v16,         :v17,       :v18 )";
                cmd.Parameters.Add(":v1", OracleType.Number).Value = order.OrderId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = order.ClienId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = order.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = order.OriginalQty;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = order.Price;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = order.OrderDateTime;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = order.OrderType;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = order.BrokerRef;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = order.Tif;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = order.TifDays;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = order.LotType;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = order.OriginalOrderId;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = order.TranId;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = order.BuySell;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = order.MinFillQty;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = order.ForeignBroker;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = order.TradeId;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = order.BrokerOffice;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Add(Order order, OracleConnection con, OracleTransaction tran)
        {
            //OracleConnection con = new OracleConnection();
            try
            {
                //con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO ORDER_TAB" +
                                    "(ORDER_ID, CLIENT_ID, SECURITY_ID, ORIGINAL_QTY, PRICE, ORDER_DATE_TIME, ORDER_TYPE, BROKER_REF, TIF, TIF_DAYS, LOT_TYPE, ORIGINAL_ORDER_ID, TRAN_ID, BUY_SELL, MIN_FILL_QTY, FOREIGN_BROKER, TRADE_ID, BROKER_OFFICE )" +
                                    "VALUES(:v1, :v2,       :v3,        :v4,            :v5,        :v6,        :v7,        :v8,      :v9,  :v10,       :v11,   :v12,               :v13,   : v14,    :v15,           :v16,         :v17,       :v18 )";
                cmd.Parameters.Add(":v1", OracleType.Number).Value = order.OrderId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = order.ClienId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = order.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.Number).Value = order.OriginalQty;
                cmd.Parameters.Add(":v5", OracleType.Number).Value = order.Price;
                cmd.Parameters.Add(":v6", OracleType.VarChar).Value = order.OrderDateTime;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = order.OrderType;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = order.BrokerRef;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = order.Tif;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = order.TifDays;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = order.LotType;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = order.OriginalOrderId;
                cmd.Parameters.Add(":v13", OracleType.VarChar).Value = order.TranId;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = order.BuySell;
                cmd.Parameters.Add(":v15", OracleType.Number).Value = order.MinFillQty;
                cmd.Parameters.Add(":v16", OracleType.Number).Value = order.ForeignBroker;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = order.TradeId;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = order.BrokerOffice;

                cmd.Connection = con;
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw exc;
            }
            finally
            {
                //con.Close();
            }
        }


        public static void Delete(string orderId)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM ORDER_TAB WHERE ORDER_ID = '" + orderId + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Order order)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE ORDER_TAB SET " +
                                  "CLIENT_ID = :v2, " +
                                  "SECURITY_ID = :v3, " +
                                  "ORIGINAL_QTY = :v4, " +
                                  "PRICE = :v5, " +
                                  "ORDER_DATE_TIME = :v6, " +
                                  "ORDER_TYPE = :v7, " +
                                  "BROKER_REF = :v8, " +
                                  "TIF = :v9, " +
                                  "TIF_DAYS = :v10, " +
                                  "LOT_TYPE = :v11, " +
                                  "ORIGINAL_ORDER_ID = :v12, " +
                                  "TRAN_ID = :v13, " +
                                  "BUY_SELL = :v14, " +
                                  "MIN_FILL_QTY = :v15, " +
                                  "FOREIGN_BROKER = :v16, " +
                                  "TRADE_ID = :v17, " +
                                  "BROKER_OFFICE = :v18 " +
                                  "WHERE ORDER_ID = :v1";


                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = order.OrderId;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = order.ClienId;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = order.SecurityId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = order.OriginalQty;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = order.Price;
                cmd.Parameters.Add(":v6", OracleType.DateTime).Value = order.OrderDateTime;
                cmd.Parameters.Add(":v7", OracleType.VarChar).Value = order.OrderType;
                cmd.Parameters.Add(":v8", OracleType.VarChar).Value = order.BrokerRef;
                cmd.Parameters.Add(":v9", OracleType.VarChar).Value = order.Tif;
                cmd.Parameters.Add(":v10", OracleType.VarChar).Value = order.TifDays;
                cmd.Parameters.Add(":v11", OracleType.VarChar).Value = order.LotType;
                cmd.Parameters.Add(":v12", OracleType.VarChar).Value = order.OriginalOrderId;
                cmd.Parameters.Add(":v13", OracleType.Number).Value = order.TranId;
                cmd.Parameters.Add(":v14", OracleType.VarChar).Value = order.BuySell;
                cmd.Parameters.Add(":v15", OracleType.VarChar).Value = order.MinFillQty;
                cmd.Parameters.Add(":v16", OracleType.VarChar).Value = order.ForeignBroker;
                cmd.Parameters.Add(":v17", OracleType.VarChar).Value = order.TradeId;
                cmd.Parameters.Add(":v18", OracleType.VarChar).Value = order.BrokerOffice;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

    }
}

